package nc.bs.ia.sys.report;

import nc.bs.dao.BaseDAO;
import nc.jdbc.framework.util.SQLHelper;
import nc.vo.hg.ia.report.IaInvInOutReportVO;
import nc.vo.hg.ia.report.InOutSumPubVO;
import nc.vo.scm.pu.PuPubVO;

public class OutSumBO extends AbstractInOutSumQryBO {

	public OutSumBO(BaseDAO dao) {
		super(dao);
	}

	@Override
	public String buildSql(String date1, String date2, String whereCondition) {
		
		StringBuffer str = new StringBuffer();
		
		str.append(" select  h.pk_corp,h.crdcenterid,sum(coalesce(b.nnumber, 0)) as nnum, sum(coalesce(b.nmoney, 0)) as ninmny,sum(coalesce(b.nplanedmny, 0)) as nmny ");
		
		if(!InOutSumPubVO.isinvcl.booleanValue()){
			str.append(",inv.pk_invbasdoc cinvbasid,b.cinventoryid");
		}else{
			str.append(",substr(invcode,0,2) cinvclid");
		}
		
		str.append(" from ia_bill h join ia_bill_b b on h.cbillid = b.cbillid and h.dr = 0 and b.dr = 0 and h.bdisableflag = 'N' and h.cbilltypecode in ('I5', 'I6', 'I7', 'I8', 'IA', 'IC', 'IJ') ");
		str.append(" inner join bd_invmandoc man on man.pk_invmandoc = b.cinventoryid inner join bd_invbasdoc inv on inv.pk_invbasdoc = man.pk_invbasdoc ");
		str.append(" where (h.pk_corp = '"+SQLHelper.getCorpPk()+"' and b.pk_corp = '"+SQLHelper.getCorpPk()+"' and b.dauditdate >= '"+date1+"' and b.dauditdate <= '"+date2+"') ");
		
		if(PuPubVO.getString_TrimZeroLenAsNull(whereCondition)!= null)
			str.append(" and "+ whereCondition);
		str.append(" group by h.pk_corp,h.crdcenterid");
		
		if(InOutSumPubVO.isinvcl.booleanValue()){
			str.append(",substr(invcode,0,2)");
		}else{
			str.append(",inv.pk_invbasdoc,b.cinventoryid");
		}
		str.append(" having sum(coalesce(b.nnumber, 0)) != 0 or sum(coalesce(b.nmoney, 0)) != 0 or sum(coalesce(b.nplanedmny, 0)) != 0 ");
		return str.toString();
	}

	@Override
	protected void appData2(IaInvInOutReportVO data, InOutSumPubVO num) {
		if(data == null || num == null)
			return;
		data.setNlastestnum(num.getNnum());
		data.setNlastestmny(num.getNmny());
	}

}
